﻿using Domain.Entity;
using DtoFw;
using DtoFw.Interface;
using Repository;
using Repository.Base;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Service
{
    public class ThreedService
    {
        private readonly RoomAreaRepository _roomAreaRepository;
        public ThreedService(RoomAreaRepository roomAreaRepository)
        {
            this._roomAreaRepository = roomAreaRepository;
        }
        /// <summary>
        /// 获取所有仓库，物品数量
        /// </summary>
        /// <param name="organId"></param>
        public List<dynamic> GetWarehouse(long organId)
        {
            string sql = @$"select s1.Id as warehouseID,
                                   s1.`Name` as warehouseName,
	                               s1.`Code` as warehouseNo,
			                         (
				                          select IFNULL(sum(EviCount),0)
                                            from room_cabinet t1 
						                    join room_box t2 on t1.Id=t2.CabinetId and t2.IsDeleted=0
                                           where t1.AreaId=s1.Id
						                     and t1.IsDeleted=0
		                           ) as inCount
                              from room_area s1 
                             where IsDeleted=0 
                               and s1.OrganId={organId}  ";

            return this._roomAreaRepository.GetDb().Ado.SqlQuery<dynamic>(sql);
        }
        /// <summary>
        /// 获取仓库设备物品数量接口
        /// </summary>
        /// <param name="warehouseID"></param>
        public List<dynamic> GetDevicesByWid(long warehouseID)
        {
            string sql = @$"select s1.Id as deviceID,
		                           s1.`No` as deviceNo,
		                           s1.`Name` as deviceName,
                                   s1.`Columns`,
	                                 (
				                       select IFNULL(sum(EviCount),0)
                                         from room_box t2 
                                        where t2.CabinetId=s1.Id
						                  and t2.IsDeleted=0
		                               ) as inCount
                              from room_cabinet s1 
                             where s1.IsDeleted=0
                               and s1.AreaId={warehouseID} ";
            return this._roomAreaRepository.GetDb().Ado.SqlQuery<dynamic>(sql);
        }
        /// <summary>
        /// 获取仓库设备物品数量接口
        /// </summary>
        /// <param name="deviceID"></param>
        public List<dynamic> GetDeviceDetail(long deviceID, int col, int direction)
        {
            string sql = @$"select s1.Id as cellID,
		                           s1.`Name` as cellName,
		                           s1.EviCount as inCount
                          from room_box s1
                         where s1.IsDeleted=0
                           and s1.CabinetId={deviceID} ";
            if (col >= 1)
                sql += " and s1.ColNO=" + col;

            if (direction == 0 || direction == 1)
                sql += " and s1.Direction=" + direction;
            return this._roomAreaRepository.GetDb().Ado.SqlQuery<dynamic>(sql);
        }

        public List<dynamic> GetDeviceCellWplist(long cellID)
        {
            string sql = @$"select s3.id,
                                   s4.CaseNo as caseCode,
		                               s4.caseName,
                                   s3.`No` as propertyCode,
                                   s3.`Name` as propertyName,
                                   s3.quantity,
                                   s5.ComNo as corpCode,
			                             s4.HostUserOrganName as corpNmae 
                              from room_box s1 
                              join case_physical_evidence_transfere s2 on s1.id=s2.`LocationId` and s2.`HistoryStatus`=0 and s2.`IsDeleted`=0 and s2.`EvidenceStatus`=1 
                              join case_physical_evidence s3 on s2.EvidenceId=s3.id 
                              join cases s4 on s3.CaseId=s4.id 
                              left join system_organ s5 on s4.HostUserOrgan=s5.Id
                             where s1.id={cellID} ";
            return this._roomAreaRepository.GetDb().Ado.SqlQuery<dynamic>(sql);
        }
        public List<dynamic> GetDeviceWplist(long deviceID)
        {
            string sql = @$"select s3.id,
                                   s4.CaseNo as caseCode,
		                               s4.caseName,
                                   s3.`No` as propertyCode,
                                   s3.`Name` as propertyName,
                                   s3.quantity,
                                   s5.ComNo as corpCode,
			                       s4.HostUserOrganName as corpNmae,
			                       s1.id as cellID,
			                       s1.`Name` as cellName 
                              from room_box s1 
                              join case_physical_evidence_transfere s2 on s1.id=s2.`LocationId` and s2.`HistoryStatus`=0 and s2.`IsDeleted`=0 and s2.`EvidenceStatus`=1 
                              join case_physical_evidence s3 on s2.EvidenceId=s3.id 
                              join cases s4 on s3.CaseId=s4.id 
                              left join system_organ s5 on s4.HostUserOrgan=s5.Id
                             where s1.cabinetId={deviceID} ";
            return this._roomAreaRepository.GetDb().Ado.SqlQuery<dynamic>(sql);
        }
        public dynamic GetWpDetailByID(long id)
        {
            string sql = @$"select s3.id,
			                       s4.CaseNo as caseCode,
			                       s4.caseName,
			                       s3.`No` as `code`,
			                       s3.`name`,
			                       s3.quantity,
			                       CONCAT(s3.ClassName,'-',s3.CategoryName,'-',s3.subCategoryName) as typeName,
			                       s3.CreateTime as registerTime,
			                       s5.ComNo as corpCode,
		                           s4.HostUserOrganName as corpName,
	                               s6.UserNo as organiserCode,
		                           s4.HostUserName as organiserName,
			                       s3.unit,
                                   s3.ExtractionPersonName as colour,
	                               s3.Source as specificationModel,
			                       s3.caiQuChuoSiDate as forceDate,
	                               s3.`Describe` as `specific`,
                                   s3.ExtractMethod as accessMethod,
                                   s3.ExtractionParts as origin,
                                   s3.chengse as fineness 
			                  from case_physical_evidence s3 
			                  join cases s4 on s3.CaseId=s4.id 
			                  left join system_organ s5 on s4.HostUserOrgan=s5.Id 
		                      left join system_user s6 on s4.HostUserId=s6.Id
		                      where s3.id={id} ";
            return this._roomAreaRepository.GetDb().Ado.SqlQuery<dynamic>(sql);
        }

        public object GetEnvironments(long organId)
        {
            var list = _roomAreaRepository.GetDb().Queryable<WarnEnvironmentEntity>().Where(x => x.OrganId == organId && x.CreateTime >= DateTime.Now.AddDays(-3)).ToList();
            if (list == null || list.Count == 0)
                return null;
            var firstG = list.GroupBy(v => v.ComNo);

            List<dynamic> vList = new List<dynamic>();
            foreach (var item in firstG)
            {
                var _vItem = item.FirstOrDefault();
                vList.Add(new
                {
                    code = item.Key,
                    name = _vItem.Area,
                    temperature = item.Where(v => v.Mold == 5).FirstOrDefault()?.ValInfo,
                    humidity = item.Where(v => v.Mold == 6).FirstOrDefault()?.ValInfo,
                    smoke = (item.Where(v => v.Mold == 7).FirstOrDefault()?.IsDeleted ?? false) ? "异常" : "正常",
                    water = (item.Where(v => v.Mold == 8).FirstOrDefault()?.IsDeleted ?? false) ? "异常" : "正常",
                });
            }
            return vList;
        }
    }
}
